package com.cpecc.dao.wuzi;

import com.cpecc.model.system.Dep;
import com.cpecc.model.system.User;
import com.cpecc.model.wuzi.WuZi;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.util.*;

@Repository
public class WuZiDao {

	@Resource
	private SessionFactory sessionFactory;
	
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	public boolean updateDepByUserId(User user) {
		String hql = "update WuZi t set t.dep = ? where t.user.id = ?";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter(0, user.getDep());
		query.setString(1, user.getId());
		return query.executeUpdate() > 0;
	}

	@SuppressWarnings("unchecked")
	public List<WuZi> getMyWuZi(User user){
		String hql="from WuZi t where t.user.id=?";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, user.getId());
		return query.list();
	}
	
	public void save(WuZi wuZi){
		Session session=sessionFactory.getCurrentSession();
		session.save(wuZi);
		session.flush();
	}
	
	public int getNo(WuZi wuZi){
		String hql="from WuZi t where t.gouMaiRQ=? and t.wuZiLB=? and t.ziChanBH not null";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter(0, wuZi.getGouMaiRQ());
		query.setParameter(1, wuZi.getWuZiLB());
		return query.list().size();
	}
	
	public boolean cover(WuZi wuZi){
		String hql="update WuZi t set t.ziChanBH=?,t.wuLiaoBM=?,t.wuZiMC=?,t.pinPai=?,t.shengChanCJ=?,t.guiGeXH=?,t.gongYingShangMC=?,t.wuZiYZ=?,t.chanPinXLH=?,t.jingCanZhiLv=?,t.zengJiaFS=?,t.zheJiuYX=?,t.baoFeiYX=?,t.zheJiuYS=?,t.baoXiuJZRQ=?,t.zheJiuFS=?,t.ruKuRQ=?,t.cunFangD=?,t.shiYongZT=?,t.dep=?,t.user=?,t.panDian=?,t.guanLiBM=?,t.beiZhu=?,t.chuKuRQ=?,t.parent=?,t.leiJiZJJE=?,t.wuZiJingZhi=?,t.ziChanBM=?,t.panDianRQ=?,t.kaiShiJiTiRQ=? where t.id=?";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, wuZi.getZiChanBH());
		query.setString(1, wuZi.getWuLiaoBM());
		query.setString(2, wuZi.getWuZiMC());
		query.setString(3, wuZi.getPinPai());
		query.setString(4, wuZi.getShengChanCJ());
		query.setString(5, wuZi.getGuiGeXH());
		query.setString(6, wuZi.getGongYingShangMC());
		query.setDouble(7, wuZi.getWuZiYZ());
		query.setString(8, wuZi.getChanPinXLH());
		query.setFloat(9, wuZi.getJingCanZhiLv());
		query.setString(10, wuZi.getZengJiaFS());
		query.setInteger(11, wuZi.getZheJiuYX());
		query.setInteger(12, wuZi.getBaoFeiYX());
		query.setInteger(13, wuZi.getZheJiuYS());
		query.setParameter(14, wuZi.getBaoXiuJZRQ());
		query.setParameter(15, wuZi.getZheJiuFS());
		query.setParameter(16, wuZi.getRuKuRQ());
		query.setString(17, wuZi.getCunFangD());
		query.setParameter(18, wuZi.getShiYongZT());
		query.setParameter(19, wuZi.getDep());
		query.setParameter(20, wuZi.getUser());
		query.setInteger(21, wuZi.getPanDian());
		query.setParameter(22, wuZi.getGuanLiBM());
		query.setString(23, wuZi.getBeiZhu());
		query.setParameter(24, wuZi.getChuKuRQ());
		query.setParameter(25, wuZi.getParent());
		query.setDouble(26, wuZi.getLeiJiZJJE());
		query.setDouble(27, wuZi.getWuZiJingZhi());
		query.setString(28, wuZi.getZiChanBM());
		query.setParameter(29, wuZi.getPanDianRQ());
		query.setParameter(30, wuZi.getKaiShiJiTiRQ());
		query.setString(31, wuZi.getId());
		return query.executeUpdate()>0;
	}
	
	public void cover1(WuZi wuZi){
		String hql="update WuZi t set t.zheJiuYS=?,t.leiJiZJJE=?,t.wuZiJingZhi=? where t.id=?";
		Session session=sessionFactory.openSession();
		Query query=session.createQuery(hql);
		query.setInteger(0, wuZi.getZheJiuYS());
		query.setDouble(1, wuZi.getLeiJiZJJE());
		query.setDouble(2, wuZi.getWuZiJingZhi());
		query.setString(3, wuZi.getId());
		query.executeUpdate();
		session.close();
	}
	
	public WuZi get(String id){
		return (WuZi) sessionFactory.getCurrentSession().get(WuZi.class, id);
	}
	
	public boolean quChuDiuShi(){
		String hql="update WuZi t set t.panDian=6 where t.shiYongZT=110";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		return query.executeUpdate()>0;
	}
	
	public boolean stopPanDian(){
		String hql="update WuZi t set t.panDian=8 where t.panDian=1";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		return query.executeUpdate()>0;
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> listStop(){
		String hql="from WuZi t where t.panDian=8";
		return sessionFactory.getCurrentSession().createQuery(hql).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getYiChangList(WuZi wuZi,String depId,String userId){
		Map<String, Object> map = new HashMap<>();
		StringBuffer hql = new StringBuffer("from WuZi t where 1=1");
		if (StringUtils.hasText(wuZi.getWuZiMC())) {
			hql.append(" and t.wuZiMC like :wuZiMC");
			map.put("wuZiMC", "%" + wuZi.getWuZiMC() + "%");
		}

		if (StringUtils.hasText(depId)) {
			hql.append(" and (t.dep.id = :depId or t.parent.id = :depId)");
			map.put("depId", depId);
		}

		if (StringUtils.hasText(userId)) {
			hql.append(" and t.user.id = :userId");
			map.put("userId", userId);
		}

		if (StringUtils.hasText(wuZi.getGuiGeXH())) {
			hql.append(" and t.guiGeXH like :guiGeXH");
			map.put("guiGeXH", "%" + wuZi.getGuiGeXH() + "%");
		}
		if (StringUtils.hasText(wuZi.getChanPinXLH())) {
			hql.append(" and t.chanPinXLH like :chanPinXLH");
			map.put("chanPinXLH", "%" + wuZi.getChanPinXLH() + "%");
		}

		if (wuZi.getWuZiLX() != 0) {
			hql.append(" and t.wuZiLX = :wuZiLX");
			map.put("wuZiLX", wuZi.getWuZiLX());
		}

		if (wuZi.getPanDian() > -1) {
			hql.append(" and t.panDian = :panDian");
			map.put("panDian", wuZi.getPanDian());
		}

		Query query=sessionFactory.getCurrentSession().createQuery(hql.toString());
		query.setProperties(map);
		return query.list();
	}
	
	public void faQiPanDian(){	
		Session session=sessionFactory.getCurrentSession();
		String hql="update WuZi t set t.panDian=1,t.panDianRQ=? where t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=106 or t.shiYongZT.id=107 or t.shiYongZT.id=108 or t.shiYongZT.id=111 or t.shiYongZT.id=112 or t.shiYongZT.id=113 or t.shiYongZT=114";
		Query query = session.createQuery(hql);
		query.setParameter(0, new Date());
		query.executeUpdate();
		session.flush();
	}
	
	public void danDuPanDian(String id){
		String hql="update WuZi t set t.panDian=1,t.panDianRQ=? where t.id=?";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter(0, new Date());
		query.setString(1, id);
		query.executeUpdate();
	}
	
	@SuppressWarnings("unchecked")
	public List<User> getPanDianYH(){
		String hql="select t.user from WuZi t where t.panDian=0 and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=106 or t.shiYongZT.id=107 or t.shiYongZT.id=108 or t.shiYongZT.id=111 or t.shiYongZT.id=112 or t.shiYongZT.id=113 or t.shiYongZT=114)";
		return sessionFactory.getCurrentSession().createQuery(hql).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getPanDianWuZi(){
		String hql="from WuZi t where t.panDian=0 and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=106 or t.shiYongZT.id=107 or t.shiYongZT.id=108 or t.shiYongZT.id=111 or t.shiYongZT.id=112 or t.shiYongZT.id=113 or t.shiYongZT=114)";
		return sessionFactory.getCurrentSession().createQuery(hql).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getMyPanDian(User user){
		String hql="from WuZi t where t.panDian=1 and t.user.id=? order by t.chuKuRQ asc";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, user.getId());
		return query.list();
	}

	public List<WuZi> getMyWuZiList(WuZi wuZi,User user,String wuZiLBId,int wuZiZTId){
		return getAllWuZiListCX(wuZi, wuZiLBId, wuZiZTId, user.getDep().getId(), user.getId());
	}
	public List<WuZi> getWuZiList(WuZi wuZi, String wuZiLBId, int[] shiYongZTId, String depId,String userId){
		Map<String, Object> map = new HashMap();


		StringBuffer sb = new StringBuffer(" from WuZi t where 1=1");

		if (StringUtils.hasText(depId)) {
			sb.append(" and t.dep.id = :depId");
			map.put("depId", depId);
		}

		if (StringUtils.hasText(userId)) {
			sb.append(" and t.user.id = :userId");
			map.put("userId", userId);
		}

		if (StringUtils.hasText(wuZi.getZiChanBH())) {
			sb.append(" and t.ziChanBH like :ziChanBH");
			map.put("ziChanBH", "%" + wuZi.getZiChanBH() + "%");
		}
		if (StringUtils.hasText(wuZi.getWuZiMC())) {
			sb.append(" and t.wuZiMC like :wuZiMC");
			map.put("wuZiMC", "%" + wuZi.getWuZiMC() + "%");
		}
		if (StringUtils.hasText(wuZiLBId)) {
			sb.append(" and t.wuZiLB.id = :wuZiLBId");
			map.put("wuZiLBId", wuZiLBId);
		}
		if (StringUtils.hasText(wuZi.getGuiGeXH())) {
			sb.append(" and t.guiGeXH like :guiGeXH");
			map.put("guiGeXH", "%" + wuZi.getGuiGeXH() + "%");
		}
		if (StringUtils.hasText(wuZi.getChanPinXLH())) {
			sb.append(" and t.chanPinXLH like :chanPinXLH");
			map.put("chanPinXLH", "%" + wuZi.getChanPinXLH() + "%");
		}
		if (shiYongZTId != null && shiYongZTId.length > 0) {
			List<Integer> list = CollectionUtils.arrayToList(shiYongZTId);
			sb.append(" and t.shiYongZT.id in (:shiYongZTId)");
			map.put("shiYongZTId", list);
		}

		Query query=sessionFactory.getCurrentSession().createQuery(sb.toString());
		query.setProperties(map);
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<WuZi> getKuCunListCX(WuZi wuZi,String wuZiLBId){
		if(wuZi.getWuZiMC()==null) wuZi.setWuZiMC("");
		if(wuZi.getZiChanBH()==null) wuZi.setZiChanBH("");
		if(wuZiLBId==null) wuZiLBId="";
		if(wuZiLBId.equals("请选择...")) wuZiLBId="";
		if(wuZi.getGuiGeXH()==null) wuZi.setGuiGeXH("");
		if(wuZi.getChanPinXLH()==null) wuZi.setChanPinXLH("");
		String hql="from WuZi t where (t.shiYongZT.id=105 or t.shiYongZT.id=106) and t.ziChanBH like? and t.wuZiMC like? and t.guiGeXH like? and t.chanPinXLH like?";
		if(wuZi.getWuZiLX()!=0){			
			hql+=" and t.wuZiLX=?";
		}else{
			hql+=" and (t.wuZiLX=? or t.wuZiLX=3 or t.wuZiLX!=3)";
		}
		if(!wuZiLBId.equals("")){			
			hql+=" and t.wuZiLB.id=?";
		}else{
			hql+=" and (t.wuZiLB.id=? or t.wuZiLX=3 or t.wuZiLX!=3)";
		}
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setInteger(0, wuZi.getWuZiLX());
		query.setString(0, "%"+wuZi.getZiChanBH()+"%");
		query.setString(1, "%"+wuZi.getWuZiMC()+"%");		
		query.setString(2, "%"+wuZi.getGuiGeXH()+"%");
		query.setString(3, "%"+wuZi.getChanPinXLH()+"%");
		query.setInteger(4, wuZi.getWuZiLX());
		query.setString(5, wuZiLBId);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getAllWuZiListCX(WuZi wuZi,String wuZiLBId,int wuZiZTId,String deptId,String userId){
		Map<String, Object> map = new HashMap();
		StringBuffer sb = new StringBuffer(" from WuZi t where 1=1");

		if (StringUtils.hasText(deptId)) {
			sb.append(" and t.dep.id = :deptId");
			map.put("deptId", deptId);
		}
		if (StringUtils.hasText(userId)) {
			sb.append(" and t.user.id = :userId");
			map.put("userId", userId);
		}
		if (StringUtils.hasText(wuZi.getZiChanBH())) {
			sb.append(" and t.ziChanBH like :ziChanBH");
			map.put("ziChanBH", "%" + wuZi.getZiChanBH() + "%");
		}
		if (StringUtils.hasText(wuZi.getWuZiMC())) {
			sb.append(" and t.wuZiMC like :wuZiMC");
			map.put("wuZiMC", "%" + wuZi.getWuZiMC() + "%");
		}
		if (StringUtils.hasText(wuZiLBId)) {
			sb.append(" and t.wuZiLB.id = :wuZiLBId");
			map.put("wuZiLBId", wuZiLBId);
		}
		if (StringUtils.hasText(wuZi.getGuiGeXH())) {
			sb.append(" and t.guiGeXH like :guiGeXH");
			map.put("guiGeXH", "%" + wuZi.getGuiGeXH() + "%");
		}
		if (StringUtils.hasText(wuZi.getChanPinXLH())) {
			sb.append(" and t.chanPinXLH like :chanPinXLH");
			map.put("chanPinXLH", "%" + wuZi.getChanPinXLH() + "%");
		}
		if (wuZiZTId != 0) {
			sb.append(" and t.shiYongZT.id = :shiYongZT");
			map.put("shiYongZT", wuZiZTId);
		}

		Query query=sessionFactory.getCurrentSession().createQuery(sb.toString());
		query.setProperties(map);
		return query.list();
	}
	
	public int getBianHao(String qianZhui){
		String hql="from WuZi t where t.ziChanBH like?";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, qianZhui+"%");
		return query.list().size()+1;		
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getAll(){
		String hql="from WuZi";
		return sessionFactory.getCurrentSession().createQuery(hql).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getBuMenWuZiListCX(Dep dep,WuZi wuZi,String wuZiLBId,int wuZiZTId){
		if(wuZi.getWuZiMC()==null) wuZi.setWuZiMC("");
		if(wuZi.getZiChanBH()==null) wuZi.setZiChanBH("");
		if(wuZiLBId==null) wuZiLBId="";
		if(wuZiLBId.equals("请选择...")) wuZiLBId="";
		if(wuZi.getGuiGeXH()==null) wuZi.setGuiGeXH("");
		if(wuZi.getChanPinXLH()==null) wuZi.setChanPinXLH("");
		String hql="from WuZi t where (t.dep.id=? or t.dep.parent.id=?) and t.ziChanBH like? and t.wuZiMC like? and t.guiGeXH like? and t.chanPinXLH like?";
		if(!wuZiLBId.equals("")){			
			hql+=" and t.wuZiLB.id=?";
		}else{
			hql+=" and (t.wuZiLB.id=? or t.shiYongZT.id=1 or t.shiYongZT.id!=1)";
		}
		if(wuZiZTId!=0){			
			hql+=" and t.shiYongZT.id=?";
		}else{
			hql+=" and (t.shiYongZT.id=? or t.shiYongZT.id=1 or t.shiYongZT.id!=1)";
		}
		if(wuZi.getWuZiLX()!=0){			
			hql+=" and t.wuZiLX=?";
		}else{
			hql+=" and (t.wuZiLX=? or t.shiYongZT.id=1 or t.shiYongZT.id!=1)";
		}
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, dep.getId());
		query.setString(1, dep.getId());
		query.setString(2, "%"+wuZi.getZiChanBH()+"%");
		query.setString(3, "%"+wuZi.getWuZiMC()+"%");		
		query.setString(4, "%"+wuZi.getGuiGeXH()+"%");
		query.setString(5, "%"+wuZi.getChanPinXLH()+"%");
		query.setString(6, wuZiLBId);
		query.setInteger(7, wuZiZTId);
		query.setInteger(8, wuZi.getWuZiLX());
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getAllBaoFeiKu(WuZi wuZi,String wuZiLBId){
		if(wuZi.getChanPinXLH()==null)
			wuZi.setChanPinXLH("");
		if(wuZi.getGuiGeXH()==null)
			wuZi.setGuiGeXH("");
		if(wuZi.getWuZiMC()==null)
			wuZi.setWuZiMC("");
		if(wuZiLBId==null)
			wuZiLBId="";
		if(wuZiLBId.equals("请选择..."))
			wuZiLBId="";		
		String hql="from WuZi t where t.shiYongZT.id=106 and t.chanPinXLH like? and t.guiGeXH like? and t.wuZiMC like?";
		if(!wuZiLBId.equals("")){
			hql+=" and t.wuZiLB.id=?";
		}else{
			hql+=" and (t.wuZiLB.id=? or t.shiYongZT.id=1 or t.shiYongZT.id!=1)";
		}
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, "%"+wuZi.getChanPinXLH()+"%");
		query.setString(1, "%"+wuZi.getGuiGeXH()+"%");
		query.setString(2, "%"+wuZi.getWuZiMC()+"%");
		query.setString(3, wuZiLBId);
		return query.list();
		
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getAllZheJiu(){
		Date dd=new Date();
		Calendar calendar=Calendar.getInstance();
		calendar.setTime(dd);
		calendar.add(Calendar.DATE, -30);
		Date yy=calendar.getTime();
		String hql="from WuZi t where t.wuZiLX=1 and t.zheJiuFS.no<5 and t.zheJiuYS<t.zheJiuYX and ((t.kaiShiJiTiRQ is not null and t.kaiShiJiTiRQ<=?) or t.kaiShiJiTiRQ is null) and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=106 or t.shiYongZT.id=111 or t.shiYongZT.id=112)";
		Session session=sessionFactory.openSession();
		Query query=session.createQuery(hql);
		query.setParameter(0, yy);
		List<WuZi> list=query.list();
		session.close();
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<User> getYuBaoFeiUsers(){		
		String hql="select t.user from WuZi t where t.wuZiLX=1 and t.zheJiuYX-t.zheJiuYS<13 and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=111 or t.shiYongZT.id=112 or t.shiYongZT.id=113 or t.shiYongZT.id=114) group by t.user";
		Session session=sessionFactory.openSession();
		List<User> list=session.createQuery(hql).list();
		session.close();
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getYuBaoFeiForUser(User user){
		String hql="from WuZi t where t.wuZiLX=1 and t.zheJiuYX-t.zheJiuYS<13 and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=106 or t.shiYongZT.id=111 or t.shiYongZT.id=112) and t.user=?";
		Session session=sessionFactory.openSession();
		Query query=session.createQuery(hql);
		query.setParameter(0, user);
		List<WuZi> list=query.list();
		session.close();
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> getAllZheJiu2(){
		String hql="from WuZi t where t.wuZiLX=1 and t.zheJiuFS.no=5 and t.zheJiuYS<t.zheJiuYX and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=111 or t.shiYongZT.id=112 or t.shiYongZT.id=113 or t.shiYongZT.id=114)";
		Session session=sessionFactory.openSession();
		Query query=session.createQuery(hql);		
		List<WuZi> list=query.list();
		session.close();
		return list;
	}
	
	public WuZi getByZiChanBH(String ziChanBH){
		String hql="from WuZi t where t.ziChanBH=?";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, ziChanBH);
		if(query.list().size()>0)
			return (WuZi) query.list().get(0);
		else
			return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> inUseList(User user){
		String hql="from WuZi t where t.user=? and (t.shiYongZT.id=102 or t.shiYongZT.id=103 or t.shiYongZT.id=105 or t.shiYongZT.id=111 or t.shiYongZT.id=112 or t.shiYongZT.id=113 or t.shiYongZT.id=114)";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter(0, user);
		return query.list();
	}
	
	public boolean delete(String id){
		String hql="delete from WuZi t where t.id=?";
		Query query=sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, id);
		return query.executeUpdate()>0;
	}
	
	@SuppressWarnings("unchecked")
	public List<WuZi> panDianList(){
		String hql="from WuZi t where t.panDian=1";
		return sessionFactory.getCurrentSession().createQuery(hql).list();
	}
}
